When we were buying our first house, we read a lot of books on mortgages. Many of these books had a payment table that told you how much your monthly payment would be based on the amount of loan, life of loan and interest rate. Some books even had an amortization schedule. But these tables were not very flexible to use. So we wrote a small program to calculate amortization schedule.
But we also wanted to know more things about a particular loan. How much interest would I pay over some period of time or the life of the loan? Would a 15 year loan be better than a 30 year one? How would an ARM loan compare with a fixed conventional loan in various scenarios? How would they compare if we sell the house in 2 years, 5 years or until the loan matures? What would our tax benefits be when we took out a loan? What was the bottom line cost to us when we used a 15 year versus an ARM loan?
Each time we asked a question, the program grew. Now it could answer the questions post above and more. We found it very useful when we were trying to decide on what kind of loan to take out. It even saved us lots of money. We think you may find it useful too. If you like to try it out, the following is a brief description of the program.
LOAN Functions
The Loan program calculate the following items
Payment To Date - total amount that you have paid to date;
Loan Balance - what you still owe to the bank;
Equity Payment & Accumulated Equity - payment that goes into your house; The accumulated equity also includes appreciation, if you specify an appreciation rate for your house.
Interest Paid & Interest Paid to Date - what bank gets;
Tax Benefit to Date - what you actually saved on tax when you deducted interest payments.
Investment to Date - investment you have accumulated in the house. It includes the equity you have put in the house and its appreciation if you input a none-zero equity Appreciation Rate (see section on user inputs). It also includes the savings you have from the tax deduction on the mortgage interest (Tax Benefit to Date) and the investment return of this saving may bring you if you reinvested it. (You have to input a none-zero number in Invest Return Rate, see section on user inputs.)
Net Cost to Date - how much the house, or more accurately, the loan has costed you so far. This is calculated as the Payment To Date minus Investment to Date.
Loan User Interface
Purchase Price - the total price of the house, including down payment. Required input.
% Down - percentage of down payment. Based on purchase price and % down, the program calculates the principal of the loan. Required input.
Loan Life - length of the loan in years. Required input.
Interest Rate - Interest rate you pay on the loan. Note that the rate is generally different for the 15 and 30 year loans. For conventional loan, you need to input a single rate. For ARM, however, you need to give a rate for each year through a pop up dialog. In the dialog, you either manually enter the rate for each year, or you can set the Initial Rate, Max. Cap, and Incremental Cap and use the Distribute button to fill in the interest rate in a worst case scenario. Required input.
Points - This includes both the origination fee and the points you pay to get better rate. Optional input, default to 0.
Monthly Payment - Calculated by the program.
Tax Bracket - The percentage rate at which you are taxed. With this information, the Loan program can calculate how much you are saving by deducting the mortgage interest payment. Optional input, default to 0.
Investment Rate - The rate you can get on you investment, such as saving account or mutual funds. If you invest the tax savings you get from the interest deductions, the Loan program can calculate the total tax benefit you get. Optional input, default to 0.
Appreciation - The rate at which you think your house may appreciate. With this information, the Loan program can calculate what is the actual worth of your equity. Optional input, default to 0.
Inflation Rate - Your estimated annual rate of inflation. This rate is used to convert among three possible representation of the calculation results in current, present, and future values. Optional input, default to 0.
Using the LOAN Program
Let’s say that I want to buy a house cost $100,000 and can get a loan for 90% of it. What kind of loan suits me the best? Let’s get some help from LOAN.
First, I want to see what I can get for a 30 year loan. The best rate I can get is 7.5% with 0 point. In the LOAN program, I select Conventional Loan under the Loan menu, the program creates a work sheet for conventional loan titled “Conventional Loan - 1”. Enter 100,000 for the Purchase Price, 10 for % Down, 30 for Loan Life, 7.5 for Interest Rate, and 0 for Point. Click on the Calc button or hit return key and the result is displayed.
The result shows that the Monthly Payment is $629.29 and over the life of the loan I will pay $136,545 in interests. Would I do better if I chose a 15 year loan? Checking with the loan broker, I find out that I can get a 15 year loan for 7.0% and 0 point.
Running LOAN, again choosing Conventional Loan under the Loan menu, I get a work sheet entitled “Conventional Loan - 2”. Fill in the required inputs with 7 in Interest Rate box and 15 in Loan Life box, then click the Calc button. The result shows that the Monthly Payment is $808.95, much more than the payment for 30 year loan. But at the end of 15 years, I would have paid only $55,610 in interests, much less than the 30 year case. Also the total cost for the 15 year loan is $55,610.18, but the total cost for the 30 year loan is $91,156.75 after 15 years. So 15 year loan appear to be a better deal.
But what if I factor in the tax deduction on the interest? Enter in both work sheets 28% for my tax bracket. Hit the Calc button again, and the new numbers are $40,039.33 for the cost of 15 year loan and $65,632.86 for the 30 year loan after 15 years.
Compare the total cost over the life of the loan, I find $40,039.33 for the 15 year loan and $98,312.76 for the 30 year one. This may not be a fair comparison, since the $98,312.76 is further devalued by inflation. To make a fair comparison, I should convert results for both 15 year and 30 loan analysis into present value. To do this I first enter 4% into the Inflation Rate box, hit Calc button and choose Present value under the Option menu for each work sheet. This gives me $51,536.87 and $87,942.66 as the total costs in present value for 15 and 30 year loan, respectively. Again, the 15 year loan appears to be a better deal, if I can afford it.
But what if I can’t handle the $808.95 payment for the 15 year loan? I have to do some more hunting. This time I will try the ARM Loan. The broker told me that I can get a 15 year one with 4.5% Initial Rate, 1.5% Incremental Cap, and 4% Maximum Cap, but I have to pay 1 Point. I chose the ARM Loan under the Loan menu, enter all the information as I did for the conventional loan, and enter the Initial Rate, Incremental and Max. Cap, and click the Distribute button in the interest dialog to do a worst case analysis.
For the ARM loan, the Monthly Payment for each year is shown, since it changes as interest rate changes. For the first four years, the total cost is less than both the 15 and 30 year conventional loan. But after that, the monthly payment shoots up and the cost goes up also. But if I think the interest rate will stay low and I may move within a few years, the ARM may be a better choice.
What we have shown here is just a few examples of what one can use the LOAN program. There are many other ways this program can help you, depending on what you may want to know about a loan you are considering. So give it a try.
